﻿using System;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Design;
using oh22is.SqlServer.Properties;
using Excel = Microsoft.Office.Interop.Excel;

namespace oh22is.SqlServer
{
    public partial class FrmPqRefresh : Form
    {

        private TaskHost TaskHost { get; set; }
        private IServiceProvider ServiceProvider { get; set; }
        private Connections Connections { set; get; }

        public FrmPqRefresh(IServiceProvider serviceProvider, TaskHost taskHost)
        {
            TaskHost = taskHost;
            ServiceProvider = serviceProvider;
            var cs = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService;
            if (cs != null) Connections = cs.GetConnections();

            InitializeComponent();
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            if (cbConnectionManager.SelectedIndex == -1)
            {
                MessageBox.Show(Resources.Please_define_a_Connection_Manager, Resources.SSIS_Excel_Refresh_Task, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                TaskHost.Properties["ConnectionManager"].SetValue(TaskHost, cbConnectionManager.Items[cbConnectionManager.SelectedIndex].ToString());
                TaskHost.Properties["Timeout"].SetValue(TaskHost, Convert.ToInt32(txtTimeOut.Value));
                TaskHost.Properties["DynamicTimeoutCalculation"].SetValue(TaskHost, Convert.ToBoolean(cbDynamicTimeout.Checked));
                TaskHost.Properties["CreateBackup"].SetValue(TaskHost, Convert.ToBoolean(cbCreateBackup.Checked));
                TaskHost.Properties["RefreshAll"].SetValue(TaskHost, Convert.ToBoolean(cbRefreshAll.Checked));
                var connections = clbOledbConnection.CheckedItems.Cast<object>().Aggregate<object, string>(null, (current, itemChecked) => current + (itemChecked.ToString() + "|"));
                if (!String.IsNullOrEmpty(connections))
                {
                    connections = connections.Substring(0, connections.Length - 1);
                }

                TaskHost.Properties["QueryConnections"].SetValue(TaskHost, connections);

                DialogResult = DialogResult.OK;
            }
        }

        private void FrmPqRefresh_Load(object sender, EventArgs e)
        {
            try
            {
                foreach (var cm in Connections.Cast<ConnectionManager>().Where(IsExcelConnection))
                {
                    cbConnectionManager.Items.Add(cm.Name);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Resources.An_error_occurred_while_retrieving_the_connection_manager, Resources.SSIS_Excel_Refresh_Task, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            cbConnectionManager.SelectedItem = (TaskHost.Properties["ConnectionManager"].GetValue(TaskHost) == null ? null : TaskHost.Properties["ConnectionManager"].GetValue(TaskHost).ToString());
            txtTimeOut.Value = (TaskHost.Properties["Timeout"].GetValue(TaskHost) == null ? 10000 : Convert.ToInt32(TaskHost.Properties["Timeout"].GetValue(TaskHost)));
            cbDynamicTimeout.Checked = (TaskHost.Properties["DynamicTimeoutCalculation"].GetValue(TaskHost) != null && Convert.ToBoolean(TaskHost.Properties["DynamicTimeoutCalculation"].GetValue(TaskHost)));
            cbCreateBackup.Checked = (TaskHost.Properties["CreateBackup"].GetValue(TaskHost) != null && Convert.ToBoolean(TaskHost.Properties["CreateBackup"].GetValue(TaskHost)));
            cbRefreshAll.Checked = (TaskHost.Properties["RefreshAll"].GetValue(TaskHost) != null && Convert.ToBoolean(TaskHost.Properties["RefreshAll"].GetValue(TaskHost)));

            if (TaskHost.Properties["QueryConnections"].GetValue(TaskHost) != null &&
                clbOledbConnection.Items.Count != 0)
            {
                var connection = TaskHost.Properties["QueryConnections"].GetValue(TaskHost).ToString().Split('|');
                for (var count = 0; count < clbOledbConnection.Items.Count; count++)
                {
                    foreach (var item in connection.Where(item => item == clbOledbConnection.Items[count].ToString()))
                    {
                        clbOledbConnection.SetItemChecked(count, true);
                    }
                }
            }
            else
            {
                cbRefreshAll.Checked = true;
            }


        }

        public static bool IsExcelConnection(ConnectionManager cm)
        {
            return cm.CreationName.StartsWith("EXCEL");
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            try
            {

                var connService = (IDtsConnectionService)ServiceProvider.GetService(typeof(IDtsConnectionService));
                System.Collections.ArrayList created = connService.CreateConnection("EXCEL");

                foreach (ConnectionManager cm in created)
                {
                    cbConnectionManager.Items.Insert(0, cm.Name);
                }

                if (created.Count > 0)
                {
                    cbConnectionManager.SelectedIndex = 0;
                }
                else
                {
                    cbConnectionManager.SelectedIndex = -1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Resources.An_error_occurred_while_creating_a_connection_manager, Resources.SSIS_Excel_Refresh_Task, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            DialogResult = DialogResult.Cancel;
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("http://ssisexcelrefresh.codeplex.com");
        }

        private void linkBaccaro_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.codeplex.com/site/users/view/regbac");
        }

        private void linkEngels_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.codeplex.com/site/users/view/oliver_engels");
        }

        private void linkEitelberg_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.codeplex.com/site/users/view/Tillmann");
        }

        private void cbDynamicTimeout_CheckedChanged(object sender, EventArgs e)
        {
            txtTimeOut.Enabled = !cbDynamicTimeout.Checked;
        }

        private void cbRefreshAll_CheckedChanged(object sender, EventArgs e)
        {
            clbOledbConnection.Enabled = !cbRefreshAll.Checked;
            clbOledbConnection.BackColor = (cbRefreshAll.Checked) ? SystemColors.Control : Color.White;
        }

        private void cbConnectionManager_SelectedIndexChanged(object sender, EventArgs e)
        {
            clbOledbConnection.Items.Clear();
            var cm = Connections[cbConnectionManager.Items[cbConnectionManager.SelectedIndex].ToString()];
            var file = new System.IO.FileInfo(cm.Properties["ExcelFilePath"].GetValue(cm).ToString());

            Excel.Application excelApp = new Excel.Application
            {
                Visible = false,
                DisplayAlerts = false,
                AskToUpdateLinks = false,
                AlertBeforeOverwriting = false,
                //FeatureInstall = MsoFeatureInstall.msoFeatureInstallNone,
                DefaultFilePath = file.DirectoryName
            };

            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(file.FullName,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

            try
            {
                foreach (Excel.WorkbookConnection cnn in excelWorkbook.Connections)
                {

                    if (cnn.Type.ToString() == "xlConnectionTypeOLEDB")
                    {
                        clbOledbConnection.Items.Add(cnn.OLEDBConnection.CommandText.ToString());
                    }
                }
            }
            finally
            {
                //excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                excelWorkbook.Close(0);
                Marshal.FinalReleaseComObject(excelWorkbook);
                excelWorkbook = null;

                excelApp.Quit();
                Marshal.FinalReleaseComObject(excelApp);
                excelApp = null;
            }
        }

        private void linkSSISComponents_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("http://www.ssis-components.net");
        }
    }
}
